# -*- coding: utf-8 -*-
import re
import MySQLdb
import requests
import time
from scrapy.selector import Selector
conn = MySQLdb.connect(host="39.106.29.90",user="root",passwd="root",db="jd",charset="utf8")
cursor = conn.cursor()

from scrapy.cmdline import execute

import sys
import os
import time


def crawl_ips():
    time.sleep(2)
    # 读取大象代理ip
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36"}
    orderData = {
        'tid': '557049080199641',
        'num': '20',
        'filter':'on',
        'protocol':'https',
        'category':'2',
        'delay':10,
        'sortby':'time'
    }
    res = requests.get(
         "http://tvp.daxiangdaili.com/ip/",params=orderData,
         headers=headers,timeout=60)

    selector = Selector(text=res.text)
    ip_list=re.split('\r\n' ,res.text)
    proxy_type = "HTTPS"
    for v in ip_list:
        ip = re.split(':', v)
        ip_fi = ip[0]
        port = ip[1]
        speed = '1'
        cursor.execute(
            "insert proxy_ip(ip, port, speed,proxy_type) VALUES('{0}', '{1}', '{2}','{3}')".format(
                ip_fi, port, speed,proxy_type))
        conn.commit()



class GetIPByApi(object):
    def delete_ip(self, ip):
        # 从数据库中删除无效的ip
        delete_sql = """
            delete from proxy_ip where ip='{0}'
        """.format(ip)
        cursor.execute(delete_sql)
        conn.commit()
        return True

    def judge_ip(self, ip, port):
        # 判断ip是否可用
        http_url = "https://www.douban.com/"
        proxy_url = "https://{0}:{1}".format(ip, port)
        try:
            headers = {
                "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36"}
            proxy_dict = {
                "https": proxy_url,
            }
            response = requests.get(http_url, proxies=proxy_dict,headers=headers,timeout=60)
        except Exception as e:
            print("invalid ip and port")
            self.delete_ip(ip)
            return False
        else:
            code = response.status_code
            if code == 200:
                print("effective ip")
                return True
            else:
                print("invalid ip and port")
                self.delete_ip(ip)
                return False
    def get_random_ip(self):
        # 获取ip表总数,如果剩余ip小于5就重新获取ip
        count_sql = """
                SELECT count(proxy_type) FROM proxy_ip WHERE proxy_type='HTTPS'
                """
        count = cursor.execute(count_sql)
        num = cursor.fetchall()[0][0]
        print(num)
        if num < 5:
            crawl_ips()
        # 获取随机ip
        random_sql = """SELECT ip,port FROM proxy_ip WHERE proxy_type='HTTPS'
            ORDER BY RAND()
            LIMIT 1
           """
        result = cursor.execute(random_sql)

        for ip_info in cursor.fetchall():
            ip = ip_info[0]
            port = ip_info[1]
            judge_re = self.judge_ip(ip, port)
            if judge_re:
                return "https://{0}:{1}".format(ip, port)
            else:
                return self.get_random_ip()



if __name__ == "__main__":
    get_ip_by_api = GetIPByApi()
    get_ip_by_api.get_random_ip()
